Stored procedures of niet?
Plain vanilla SQL instructie
.NET en SQL zijn twee verschillende programmeeromgevingen. Je kan niet rechtstreeks vanuit C# een SQL instructie uitvoeren. Je kan wel in C# een SQL instructie in een string stoppen en die via een Command object naar de SQL server sturen die de instructie binnen haar eigen omgeving uitvoert en het resultaat naar C# in .NET terugstuurt.
We beginnen met het maken van een string waarin we een MS SQL statement samenstellen. We gebruiken een StringBuilder object om het SQL statement overzichtelijk te houden. De methode heet InsertWithoutParameters.
using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
namespace InAntwerpen.Com.Bibliotex.DAL
{
class DALBook : Base
{
private string feedback;
public string Feedback
{
// Alleen een Getter omdat de feedback alleen
// door die klasse gegeven kan worden.
get { return feedback; }
}
public int InsertWithoutParameters()
{
StringBuilder sql = new StringBuilder();
sql.Append("insert into Book (");
sql.Append("Firstname,");
sql.Append("Lastname,");
sql.Append("Title,");
sql.Append("City, ");
sql.Append("Publisher,");
sql.Append("PublicationYear,");
sql.Append("ReprintYear, ");
sql.Append("Comment,");
sql.Append("Isbn13");
sql.Append(") ");
sql.Append("values ");
sql.Append("( ");
sql.Append("'Jean-Paul', ");
sql.Append("'Sartre', ");
sql.Append("'L''être et le néant', ");
sql.Append("'Paris', ");
sql.Append("'Gallimard', ");
sql.Append("'1943',");
sql.Append("'2005', ");
sql.Append("'Het boek weegt precies 1 kilo', ");
sql.Append("'0123456789012'");
sql.Append(")");
// connectie met de database
SqlConnection connection = new SqlConnection();
connection.ConnectionString = this.ConnectionString;
// ik ga ervan uit dat het niet gelukt
int result = -1;
try
{
connection.Open();
this.feedback = "De database Bibliotex is geopend.";
SqlCommand command = new SqlCommand();
command.Connection = connection;
command.CommandText = sql.ToString();
// voert de opdracht uit maar retourneert geen typed IDataReader
//wel het aantal rijen dat bewerkt zijn geworden
result = command.ExecuteNonQuery();
}
catch (SqlException e)
{
this.feedback = "Kan de database Bibliotex niet openen.";
}
finally
{
connection.Close();
}
return result;
}
}
}
Parameters gebruiken
Voor gedetailleerde uitleg over gebruik van parameters zie De Command klasse. Let erop dat de gegevens van het boek niet meer in de SQL rechtstreeks worden meegegeven maar via een Book object.
Hier volgt de code:
public int InsertWithParameters(Book book)
{
StringBuilder sql = new StringBuilder();
sql.Append("insert into Book (");
sql.Append("Firstname,");
sql.Append("Lastname,");
sql.Append("Title,");
sql.Append("City, ");
sql.Append("Publisher,");
sql.Append("PublicationYear,");
sql.Append("ReprintYear, ");
sql.Append("Comment,");
sql.Append("Isbn13");
sql.Append(") ");
sql.Append("values ");
sql.Append("( ");
sql.Append("@Firstname, ");
sql.Append("@Lastname, ");
sql.Append("@Title, ");
sql.Append("@City, ");
sql.Append("@Publisher, ");
sql.Append("@PublicationYear, ");
sql.Append("@ReprintYear, ");
sql.Append("@Comment, ");
sql.Append("@Isbn13");
sql.Append(")");
// parameters maken en eigenschappen instellen
SqlParameter pFirstname = new SqlParameter();
pFirstname.ParameterName = "@Firstname";
pFirstname.DbType = DbType.String;
pFirstname.Size = 50;
pFirstname.Value = book.Firstname;
// connectie met de database
SqlConnection connection = new SqlConnection();
connection.ConnectionString = this.ConnectionString;
// ik ga ervan uit dat het niet gelukt
int result = -1;
try
{
connection.Open();
this.feedback = "De database Bibliotex is geopend.";
SqlCommand command = new SqlCommand();
// De parameters toevoegen
command.Parameters.Add(pFirstname);
// Verkorte vorm
command.Parameters.Add(new SqlParameter("@Lastname", SqlDbType.Text, 80)).Value = book.Lastname;
command.Parameters.Add(new SqlParameter("@Title", SqlDbType.Text, 255)).Value = book.Title;
command.Parameters.Add(new SqlParameter("@City", SqlDbType.Text, 50)).Value = book.City;
command.Parameters.Add(new SqlParameter("@Publisher", SqlDbType.Text, 80)).Value = book.Publisher;
command.Parameters.Add(new SqlParameter("@PublicationYear", SqlDbType.Text, 4)).Value = book.PublicationYear;
command.Parameters.Add(new SqlParameter("@ReprintYear", SqlDbType.Text, 4)).Value = book.ReprintYear;
command.Parameters.Add(new SqlParameter("@Comment", SqlDbType.Text, 2000)).Value = book.Comment;
command.Parameters.Add(new SqlParameter("@Isbn13", SqlDbType.Text, 13)).Value = book.ISBN13;
// Connectie
command.Connection = connection;
command.CommandText = sql.ToString();
// voert de opdracht uit maar retourneert geen typed IDataReader
//wel het aantal rijen dat bewerkt zijn geworden
result = command.ExecuteNonQuery();
}
catch (SqlException e)
{
this.feedback = "Kan de database Bibliotex niet openen.";
}
finally
{
connection.Close();
}
return result;
}
Stored Procedure
De veiligste manier om een SQL statement vanuit C# naar SQL te sturen is met behulp van een stored procedure:
- Er kan niet gefoefeld worden met SQL statement;
- De stored procedure kan in SQL getest worden vooraleer in C# te gebruiken;
- Geen ambetante lange string maken in C# die je niet kunt testen vooraleer naar SQL te sturen;
De naam van de stored procedure stop je in een de CommandText eigenschap van het Command object en de eigenschap CommandType stel je in op StoredProcedure. De methode heet gewoon Insert want ie methode gaan we in ons project gebruiken. In andere projecten heet die methode Create omdat we daar de CRUD benamingen gebruiken.
public int BookInsert()
{
// ik ga ervan uit dat het niet gelukt
int result = -1;
try
{
connection.Open();
this.feedback = "De database BibliotexNC is geopend.";
SqlCommand command = new SqlCommand();
// De parameters toevoegen
// Verkorte vorm
command.Parameters.Add(new SqlParameter("@Title", SqlDbType.Text, 255)).
Value = this.Title;
command.Parameters.Add(new SqlParameter("@City", SqlDbType.Text, 50)).
Value = this.City;
command.Parameters.Add(new SqlParameter("@Publisher", SqlDbType.Text, 255)).
Value = this.Publisher;
command.Parameters.Add(new SqlParameter("@PublicationYear", SqlDbType.Text, 4)).
Value = this.PublicationYear;
command.Parameters.Add(new SqlParameter("@ReprintYear", SqlDbType.Text, 4)).
Value = this.ReprintYear;
command.Parameters.Add(new SqlParameter("@Comment", SqlDbType.Text, 2000)).
Value = this.Comment;
command.Parameters.Add(new SqlParameter("@Isbn13", SqlDbType.Text, 13)).
Value = this.ISBN13;
SqlParameter valid = new SqlParameter("@Output", SqlDbType.Int);
valid.Direction = ParameterDirection.Output;
command.Parameters.Add(valid);
// Connectie
command.Connection = connection;
command.CommandText = "BookInsertValidate";
// zeg dat het een Stored Procedure is
command.CommandType = CommandType.StoredProcedure;
// voert de opdracht uit maar retourneert geen typed IDataReader wel het
// aantal rijen dat bewerkt zijn geworden
result = command.ExecuteNonQuery();
int i = (int) valid.Value;
if (i == -1)
{
feedback = "Dit boek bestaat reeds";
}
else
{
result = 1;
feedback = "Dit boek werd toegevoegd";
}
}
catch (SqlException e)
{
this.feedback = "Kan de database Sources niet openen";
this.errorMessage = e.Message;
}
finally
{
connection.Close();
}
return result;
}
Opmerking
In de Parameters collectie van het Command object hebben we één Output parameter toegevoegd. Standaard zijn alle parameters ingesteld op Input. Dat wil zeggen dat we waarden van C# doorgeven naar de stored procedure. In het geval van Insert, retourneert de stored procedure echter een waarde. De nieuw Id van de net toegevoegde rij. Deze waarde kunnen we in C# nodig hebben als we bijvoorbeeld een auteur willen toevoegen. Daarvoor moeten we een rij in BookAuthor toevoegen en hebben we eventueel de Id van de nieuw toegevoegd auteur nodig.
SqlParameter valid = new SqlParameter("@Output", SqlDbType.Int);
valid.Direction = ParameterDirection.Output;
command.Parameters.Add(valid);
Nadat we de Command hebben uitgevoerd kunnen we de waarde in de Output parameter opvragen:
int i = (int) valid.Value;